Slow queries in PL/PGSQL function

Поиск
Список
Период
Сортировка
От Jim Crate
Тема Slow queries in PL/PGSQL function
Дата
Msg-id r02010100-1032-5E4696A2632F11D8B8E90003939CD378@[63.175.177.240]
обсуждение исходный текст
Ответы Re: Slow queries in PL/PGSQL function  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
I have a couple queries in a PL/PGSQL function which execute very slowly (around
one minute each) which execute in .5 second when not executed from within the
function.  Is there any way to determine why this is happening?  I couldn't
figure out how to run EXPLAIN ANALYZE from within the function.  The queries
aren't very complicated.

Here is an example query and plan.  About 240K rows in x_rbl_ips, 7.5M rows in
filter_ips.

explain analyze SELECT DISTINCT i_ip
FROM x_rbl_ips
LEFT JOIN filter_ips ON x_rbl_ips.i_ip = filter_ips.i_filter_ip
WHERE x_rbl_ips.dts_last_modified > '2004-02-18 22:24:15.901689+00'
  AND filter_ips.i_filter_ip IS NOT NULL
  AND (i_filter_type_flags & X'02000000'::integer) <> X'02000000'::integer


QUERY PLAN
Unique  (cost=5703.70..5703.81 rows=23 width=4) (actual time=524.439..524.439
rows=0 loops=1)
  ->  Sort  (cost=5703.70..5703.75 rows=23 width=4) (actual
time=524.438..524.438 rows=0 loops=1)
        Sort Key: x_rbl_ips.i_ip
        ->  Nested Loop  (cost=0.00..5703.18 rows=23 width=4) (actual
time=524.028..524.028 rows=0 loops=1)
              ->  Seq Scan on x_rbl_ips  (cost=0.00..5616.56 rows=23 width=4)
(actual time=412.738..415.842 rows=1738 loops=1)
                    Filter: (dts_last_modified > '2004-02-18
22:44:15.901689'::timestamp without time zone)
              ->  Index Scan using filter_ips_source_ip on filter_ips
(cost=0.00..3.75 rows=1 width=4) (actual time=0.061..0.061 rows=0 loops=1738)
                    Index Cond: ("outer".i_ip = filter_ips.i_filter_ip)
                    Filter: ((i_filter_ip IS NOT NULL) AND ((i_filter_type_flags
& 33554432) <> 33554432))
Total runtime: 524.868 ms



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: pg_restore problem
Следующее
От: Jim Crate
Дата:
Сообщение: Slow queries in PL/PGSQL function